﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace NCRGEA.Members
{
    public partial class frmViewMember : Form
    {
        public int MemberID;
        private bool IsEditing;
        public frmViewMember()
        {
            InitializeComponent();
            IsEditing = false;
        }

        public frmViewMember(int MemberID) : this()
        {
            this.MemberID = MemberID;
            PopulateFields();
            LoadMember();
            UpdateFields(true);
        }

        private void PopulateFields()
        {
            //Populate status types
            string qry = "SELECT TypeID, Name FROM memTypes";
            DataTable statusTypes = AAL.DAL.ExecuteQuery(qry);
            if (statusTypes != null)
            {
                cmbStatus.DataSource = statusTypes;
                cmbStatus.DisplayMember = "Name";
            }
        }

        public void UpdateFields(bool Locked)
        {
            txtSSN1.Enabled = false;
            txtSSN2.Enabled = false;
            txtSSN3.Enabled = false;
            txtFollowups.ReadOnly = Locked;
            txtGroupCode.ReadOnly = Locked;
            txtFirstName.ReadOnly = Locked;
            txtLastName.ReadOnly = Locked;
            txtMiddleName.ReadOnly = Locked;
            cmbSex.Enabled = !Locked;
            cmbStatus.Enabled = !Locked;
            dateDOB.Enabled = !Locked;
            dateRetired.Enabled = !Locked;
            txtAddress.ReadOnly = Locked;
            txtAddress2.ReadOnly = Locked;
            txtCity.ReadOnly = Locked;
            txtState.ReadOnly = Locked;
            txtZip.ReadOnly = Locked;
            txtZipFour.ReadOnly = Locked;
            txtCounty.ReadOnly = Locked;
            txtEmail.ReadOnly = Locked;
            txtEmployer.ReadOnly = Locked;
            txtPhone1.ReadOnly = Locked;
            txtPhone2.ReadOnly = Locked;
            dateRetired.Enabled = !Locked;
            dateRenewalDate.Enabled = !Locked;
            txtYearsService.ReadOnly = Locked;
            dateJoinDate.Enabled = !Locked;
            chkRetired.Enabled = !Locked;
            cmbNewsletter.Enabled = !Locked;
        }

        private void LoadQuickInfo()
        {
            //Reset the data
            txtLastPaymentAmount.Text = txtLastPaymentDate.Text = string.Empty;
            txtLastPaymentAmount.Text = txtDentalAmount.Text = txtDuesAmount.Text = txtVisionAmount.Text = "$0.000";
            string lastPmtQry = string.Format("SELECT Date [LastPmtDate], Amount [LastPmtAmount] FROM payments WHERE MemberID={0} AND PaymentID=(SELECT MAX(PaymentID) FROM payments WHERE MemberID={0})", MemberID);
            DataTable lastPmtData = AAL.DAL.ExecuteQuery(lastPmtQry);
            if (lastPmtData != null && lastPmtData.Rows.Count != 0)
            {
                txtLastPaymentDate.Text = lastPmtData.Rows[0]["LastPmtDate"].ToString();
                txtLastPaymentAmount.Text = "$" + lastPmtData.Rows[0]["LastPmtAmount"].ToString();
            }

            string qry = string.Format(@"SELECT
	                                        COALESCE(mD.Amount, 0) [DuesAmount],
	                                        COALESCE((SELECT
		                                        Amount
	                                        FROM
		                                        memInfo mI
		                                        LEFT JOIN memPlans mP ON mI.MemberID = mP.MemberID
		                                        LEFT JOIN memPrograms mPR ON mP.ProgramID = mPR.ProgramID
	                                        WHERE
		                                        mI.MemberID={0} AND mPR.ProgramTypeID=0 AND mP.Active=1),0) [DentalAmount],
	                                        COALESCE((SELECT
		                                        Amount
	                                        FROM
		                                        memInfo mI
		                                        LEFT JOIN memPlans mP ON mI.MemberID = mP.MemberID
		                                        LEFT JOIN memPrograms mPR ON mP.ProgramID = mPR.ProgramID
	                                        WHERE
		                                        mI.MemberID={0} AND mPR.ProgramTypeID=1 AND mP.Active=1),0) [VisionAmount]
                                        FROM
	                                        memInfo mI LEFT JOIN memDues mD ON mI.DuesID = mD.DuesID
                                        WHERE
	                                        mI.MemberID={0}", MemberID);
            DataTable data = AAL.DAL.ExecuteQuery(qry);
            if (data != null && data.Rows.Count != 0)
            {
                txtDuesAmount.Text = "$" + data.Rows[0]["DuesAmount"].ToString();
                txtDentalAmount.Text = "$" + data.Rows[0]["DentalAmount"].ToString();
                txtVisionAmount.Text = "$" + data.Rows[0]["VisionAmount"].ToString();
            }
        }

        public void LoadMember()
        {
            LoadQuickInfo();
            ucPayments1.MemberID = this.MemberID;
            ucPrograms1.MemberID = this.MemberID;
            ucNotes1.MemberID = this.MemberID;
            ucLogs1.MemberID = this.MemberID;
            ucDeductions1.MemberID = this.MemberID;
          
            
            string qry = @"SELECT
                                MemberID, DuesID, TypeID, DOB, FirstName, MiddleName, LastName, Address1, Address2,
                                City, State, Zip, ZipFour, Phone, Phone2, Email, Sex, IncomeRange, RetirementDate,
                                JoinDate, Employer, County, YearsService, RenewalDate, Newsletter, Deceased,
                                Enrolled, DedFlag, DentalFlag, VisionFlag, EnrollmentDate, GroupCode, NumFollowups, SpouseID,
                                LEFT(SSN,3) SSN1, SUBSTRING(SSN,4,2) SSN2, RIGHT(SSN,4) SSN3
                            FROM
                                memInfo
                            WHERE
                                MemberID=@MemberID";
            SqlParameter[] sParamArr = new SqlParameter[1];
            sParamArr[0] = new SqlParameter("@MemberID", MemberID) { SqlDbType = SqlDbType.Int };
            DataTable tbl = AAL.DAL.ExecuteQuery(qry, sParamArr);
            if (tbl == null || tbl.Rows.Count == 0)
            {
                MessageBox.Show("Failed to load member #" + this.MemberID + "!", "NCRGEA");
                this.Close();
                return;
            }
            DataRow dr = tbl.Rows[0];
            txtMemberID.Text = dr["MemberID"].ToString();
            txtSSN1.Text = dr["SSN1"].ToString();
            txtSSN2.Text = dr["SSN2"].ToString();
            txtSSN3.Text = dr["SSN3"].ToString();

            if (dr["DOB"] == DBNull.Value || ((DateTime)dr["DOB"]) == DateTime.MinValue)
                dateDOB.Value = dateDOB.MinDate;
            else
                dateDOB.Value = (DateTime)dr["DOB"];

            
            txtFirstName.Text = dr["FirstName"].ToString();
            txtLastName.Text = dr["LastName"].ToString();
            txtMiddleName.Text = dr["MiddleName"].ToString();
            txtAddress.Text = dr["Address1"].ToString();
            txtAddress2.Text = dr["Address2"].ToString();
            txtCity.Text = dr["City"].ToString();
            txtState.Text = dr["State"].ToString();
            txtZip.Text = dr["Zip"].ToString();
            txtZipFour.Text = dr["ZipFour"].ToString();
            txtPhone1.Text = dr["Phone"].ToString();
            txtPhone2.Text = dr["Phone2"].ToString();
            txtEmail.Text = dr["Email"].ToString();

            if (dr["NumFollowups"] == DBNull.Value)
                txtFollowups.Text = string.Empty;
            else
                txtFollowups.Text = dr["NumFollowups"].ToString();

            if (dr["GroupCode"] == DBNull.Value)
                txtGroupCode.Text = string.Empty;
            else
                txtGroupCode.Text = dr["GroupCode"].ToString();

            //Parse in RetirementDate
            DateTime tmpRetired = DateTime.MinValue;
            if (dr["RetirementDate"] != null && dr["RetirementDate"] != DBNull.Value)
            {
                tmpRetired = (DateTime)dr["RetirementDate"];
                dateRetired.Enabled = true;
                chkRetired.Checked = true;
            }
            if (tmpRetired < dateRetired.MinDate)
                dateRetired.Value = dateRetired.MinDate;

            //Parse in JoinDate
            DateTime tmpJoined = DateTime.MinValue;
            if (dr["JoinDate"] != null && dr["JoinDate"] != DBNull.Value)
            {
                tmpJoined = (DateTime)dr["JoinDate"];
            }
            if (tmpJoined < dateJoinDate.MinDate)
                dateJoinDate.Value = dateJoinDate.MinDate;

            txtEmployer.Text = dr["Employer"].ToString();
            txtCounty.Text = dr["County"].ToString();
            txtYearsService.Text = dr["YearsService"].ToString();

            //Parse in RenewalDate
            DateTime tmpRenewalDate = DateTime.MinValue;
            if (dr["RenewalDate"] != null && dr["RenewalDate"] != DBNull.Value)
                tmpRenewalDate = (DateTime)dr["RenewalDate"];
            if (tmpRenewalDate < dateRenewalDate.MinDate)
                dateRenewalDate.Value = dateRenewalDate.MinDate;

            string sex = dr["Sex"].ToString();
            switch (sex)
            {
                
                case "F":
                    cmbSex.SelectedIndex = 1;
                    break;
                case "M":
                default:
                    cmbSex.SelectedIndex = 0;
                    break;
            }

            int TypeID = (int)dr["TypeID"];
            for (int i = 0; i < cmbStatus.Items.Count; i++)
            {
                if ((int)((DataRowView)cmbStatus.Items[i])[0] == TypeID)
                {
                    cmbStatus.SelectedIndex = i;
                    break;
                }
            }
            string newsletter = dr["Newsletter"].ToString();
            switch (newsletter)
            {
                case "Y":
                    cmbNewsletter.SelectedIndex = 0;
                    break;
                case "N":
                    cmbNewsletter.SelectedIndex = 1;
                    break;
                case "E":
                    cmbNewsletter.SelectedIndex = 2;
                    break;
                default:
                    cmbNewsletter.SelectedIndex = 0;
                    break;
            }
            UpdateEditing();
        }

        //Trigger called by different tab controls to indicate that the member may have changed
        public void ReloadMember()
        {
            DialogResult dr = MessageBox.Show("Do you want to reload this member? Several fields may have changed.", "NCRGEA", MessageBoxButtons.YesNo);
            if (dr == DialogResult.Yes)
                LoadMember();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            IsEditing = !IsEditing;
            if (IsEditing)
                btnEdit.Text = "Cancel";
            else
                btnEdit.Text = "Edit";
            UpdateFields(!IsEditing);
            UpdateEditing();
        }

        public void UpdateEditing()
        {
            btnSave.Enabled = IsEditing;
            ((UserControls.ucNotes)tabNotes.Controls[0]).IsEditable = IsEditing;
            if (tabPayments.Controls[0] is UserControls.ucPayments)
                ((UserControls.ucPayments)tabPayments.Controls[0]).IsEditable = IsEditing;
            if (tabPrograms.Controls[0] is UserControls.ucPrograms)
                ((UserControls.ucPrograms)tabPrograms.Controls[0]).IsEditable = IsEditing;
        }

        private void dateDOB_Enter(object sender, EventArgs e)
        {
            this.Focus();
        }

        private void chkRetired_CheckedChanged(object sender, EventArgs e)
        {
            dateRetired.Enabled = chkRetired.Checked;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            if (!ValidateData())
                return;
            SaveData();            
        }

        private void SaveData()
        {
            int memberID = MemberID;
            string ssn = txtSSN1.Text + txtSSN2.Text + txtSSN3.Text;
            if (string.IsNullOrWhiteSpace(ssn))
                ssn = null;
            int typeID = (int)((DataRowView)cmbStatus.SelectedValue)["TypeID"];
            DateTime dob = dateDOB.Value;
            string sex = cmbSex.Text.Substring(0, 1).ToUpper();
            string fName = txtFirstName.Text;
            string mName = txtMiddleName.Text;
            string lName = txtLastName.Text;
            string address1 = txtAddress.Text;
            string address2 = txtAddress2.Text;
            string county = txtCounty.Text;
            string city = txtCity.Text;
            string state = txtState.Text;
            string zip = txtZip.Text;
            string zipfour = txtZipFour.Text;
            string email = txtEmail.Text;
            string phone = txtPhone1.Text;
            string phone2 = txtPhone2.Text;
            string employer = txtEmployer.Text;
            string newsletter;

            switch (cmbNewsletter.SelectedIndex)
            {
                case 0:
                    newsletter = "Y";
                    break;
                case 1:
                    newsletter = "N";
                    break;
                case 2:
                    newsletter = "E";
                    break;
                default:
                    newsletter = "Y";
                    break;
            }
            
            int groupCode;
            if (string.IsNullOrWhiteSpace(txtGroupCode.Text))
                groupCode = 0;
            else
                groupCode = int.Parse(txtGroupCode.Text);

            int numFollowups;
            if (string.IsNullOrWhiteSpace(txtFollowups.Text))
                numFollowups = 0;
            else
                numFollowups = int.Parse(txtFollowups.Text);


            DateTime retiredDate = DateTime.MinValue;
            if (chkRetired.Checked)
                retiredDate = dateRetired.Value;
            int yearsService = 0;
            if (IsInteger(txtYearsService.Text))
                yearsService = Int32.Parse(txtYearsService.Text);

            string sqlQuery = @"UPDATE memInfo SET SSN=@ssn, TypeID=@typeID, DOB=@dob, Sex=@sex, FirstName=@fName, MiddleName=@mName,
                                    LastName=@lName, Address1=@address1, Address2=@address2, City=@city, State=@state, Zip=@zip,
                                    ZipFour=@zipfour, Email=@email, Phone=@phone, Phone2=@phone2, Employer=@employer,
                                    RetirementDate=@retiredDate, YearsService=@yearsService, County=@county, GroupCode=@groupcode,
                                    NumFollowups=@numfollowups, Newsletter=@newsletter WHERE MemberID=@memberID";

            SqlParameter[] sParams = new SqlParameter[24];
            sParams[0] = new SqlParameter("@ssn", AAL.DAL.DBN(ssn)) { SqlDbType = SqlDbType.VarChar, Size = 9, IsNullable=true };
            sParams[1] = new SqlParameter("@typeID", typeID) { SqlDbType = SqlDbType.Int };
            sParams[2] = new SqlParameter("@dob", dob) { SqlDbType = SqlDbType.Date };
            sParams[3] = new SqlParameter("@sex", sex) { SqlDbType = SqlDbType.VarChar, Size = 1 };
            sParams[4] = new SqlParameter("@fName", fName) { SqlDbType = SqlDbType.VarChar, Size = 30 };
            sParams[5] = new SqlParameter("@mName", mName) { SqlDbType = SqlDbType.VarChar, Size = 30 };
            sParams[6] = new SqlParameter("@lName", lName) { SqlDbType = SqlDbType.VarChar, Size = 30 };
            sParams[7] = new SqlParameter("@address1", address1) { SqlDbType = SqlDbType.VarChar, Size = 50 };
            sParams[8] = new SqlParameter("@address2", address2) { SqlDbType = SqlDbType.VarChar, Size = 50 };
            sParams[9] = new SqlParameter("@city", city) { SqlDbType = SqlDbType.VarChar, Size = 20 };
            sParams[10] = new SqlParameter("@state", state) { SqlDbType = SqlDbType.VarChar, Size = 20 };
            sParams[11] = new SqlParameter("@zip", zip) { SqlDbType = SqlDbType.VarChar, Size = 5 };
            sParams[12] = new SqlParameter("@zipfour", zipfour) { SqlDbType = SqlDbType.VarChar, Size = 4 };
            sParams[13] = new SqlParameter("@email", email) { SqlDbType = SqlDbType.VarChar, Size = 40 };
            sParams[14] = new SqlParameter("@phone", phone) { SqlDbType = SqlDbType.VarChar, Size = 15 };
            sParams[15] = new SqlParameter("@phone2", phone2) { SqlDbType = SqlDbType.VarChar, Size = 15 };
            sParams[16] = new SqlParameter("@employer", employer) { SqlDbType = SqlDbType.VarChar, Size = 40 };
            sParams[17] = new SqlParameter("@retiredDate", retiredDate) { SqlDbType = SqlDbType.Date };
            sParams[18] = new SqlParameter("@yearsService", yearsService) { SqlDbType = SqlDbType.SmallInt };
            sParams[19] = new SqlParameter("@county", county) { SqlDbType = SqlDbType.VarChar, Size = 20 };
            sParams[20] = new SqlParameter("@memberID", memberID) { SqlDbType = SqlDbType.Int };
            sParams[21] = new SqlParameter("@groupcode", groupCode) { SqlDbType = SqlDbType.SmallInt };
            sParams[22] = new SqlParameter("@numfollowups", numFollowups) { SqlDbType = SqlDbType.SmallInt };
            sParams[23] = new SqlParameter("@newsletter", newsletter) { SqlDbType = SqlDbType.Char, Size = 1 };

            int result = AAL.DAL.ExecuteNonQuery(sqlQuery, sParams);
            if (result == 1)
                UpdateSuccessful();
            else
            {
                MessageBox.Show("Error updating member!");
                Logger.Log("Couldn't update member " + memberID + ". Rows Affected: " + result);
            }
        }

        private void UpdateSuccessful()
        {
            LoadMember();
            btnEdit_Click(null, null);
        }

        private bool ValidateData()
        {
            //Parse over data and make any quick corrections
            CheckData();

            //Validate that the SSN isn't already being used.
            string ssn = txtSSN1.Text + txtSSN2.Text + txtSSN3.Text;
            if (!string.IsNullOrWhiteSpace(ssn))
            {
                if (!IsInteger(ssn))
                {
                    ShowMessageAndFocus("Invalid SSN!", txtSSN1);
                    return false;
                }
                else
                {
                    if (ssn.Length != 9)
                    {
                        ShowMessageAndFocus("Invalid SSN!", txtSSN1);
                        return false;
                    }

                    if (!AAL.UAL.ValidateSSNChange(ssn, MemberID))
                    {
                        MessageBox.Show("This SSN is already in use by another member!");
                        txtSSN1.Focus();
                        return false;
                    }
                }
            }
            

            //Check Status
            if (cmbStatus.SelectedIndex == -1)
            {
                ShowMessageAndFocus("Please select a member status!", cmbStatus);
                return false;
            }

            //Check Sex
            if (cmbSex.SelectedIndex == -1)
            {
                ShowMessageAndFocus("Please select a member sex!", cmbSex);
                return false;
            }

            //Check First Name
            if (string.IsNullOrWhiteSpace(txtFirstName.Text))
            {
                ShowMessageAndFocus("Invalid First Name!", txtFirstName);
                return false;
            }

            //Check Last Name
            if (string.IsNullOrWhiteSpace(txtLastName.Text))
            {
                ShowMessageAndFocus("Invalid Last Name!", txtLastName);
                return false;
            }

            //Check City
            if (string.IsNullOrWhiteSpace(txtCity.Text))
            {
                ShowMessageAndFocus("Invalid City!", txtCity);
                return false;
            }

            //Check State
            if (string.IsNullOrWhiteSpace(txtState.Text) || !ValidateState(txtState.Text))
            {
                ShowMessageAndFocus("Invalid State!", txtState);
                return false;
            }

            //Check Zip (not four)
            if (string.IsNullOrWhiteSpace(txtZip.Text) || !IsInteger(txtZip.Text))
            {
                ShowMessageAndFocus("Invalid Zip!", txtZip);
                return false;
            }

            //Validate that YearsService is an integer (not required
            if (!string.IsNullOrWhiteSpace(txtYearsService.Text) && !IsInteger(txtYearsService.Text))
            {
                ShowMessageAndFocus("Invalid years of service!", txtYearsService);
                return false;
            }

            if (!string.IsNullOrWhiteSpace(txtGroupCode.Text) && !IsInteger(txtGroupCode.Text))
            {
                ShowMessageAndFocus("Invalid Group Code!", txtGroupCode);
                return false;
            }

            if (!string.IsNullOrWhiteSpace(txtFollowups.Text) && !IsInteger(txtFollowups.Text))
            {
                ShowMessageAndFocus("Invalid Number of Followups!", txtFollowups);
                return false;
            }
            return true;
        }

        private void CheckData()
        {
            if (!string.IsNullOrWhiteSpace(txtState.Text))
                txtState.Text = txtState.Text.ToUpper();
        }

        public bool ValidateState(string state)
        {
            string[] states = { "AL", "AK", "AZ", "AR", "CA", "CO", "CT", "DE", "FL", "GA", "HI", "ID", "IL", "IN", "IA", "KS", "KY", "LA", "ME", "MD", "MA", "MI", "MN", "MS", "MO", "MT", "NE", "NV", "NH", "NJ", "NM", "NY", "NC", "ND", "OH", "OK", "OR", "PA", "RI", "SC", "SD", "TN", "TX", "UT", "VT", "VA", "WA", "WV", "WI", "WY" };
            string uState = state.ToUpper();
            if (states.Contains(uState))
                return true;
            return false;
        }

        public void ShowMessageAndFocus(string msg, Control c)
        {
            MessageBox.Show(msg);
            if (c is TextBox)
                c.Text = string.Empty;
            c.Focus();
        }

        public bool IsInteger(string str)
        {
            if (string.IsNullOrWhiteSpace(str))
                return false;
            int a;
            if (Int32.TryParse(str, out a))
                return true;
            return false;
        }

        private void btnPrintForm_Click(object sender, EventArgs e)
        {
            ((frmMain)(this.ParentForm)).LaunchForm(new Reports.frmMemInfoReport(MemberID));
        }

        private void frmViewMember_KeyUp(object sender, KeyEventArgs e)
        {
            if (e.Control && e.KeyCode == Keys.E)
            {
                if (IsEditing)
                {
                    txtSSN1.Enabled = !txtSSN1.Enabled;
                    txtSSN2.Enabled = !txtSSN2.Enabled;
                    txtSSN3.Enabled = !txtSSN3.Enabled;
                }
            }
        }

        private void btnHelp_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Please press Control+E in order to lock or unlock the Social Security Number field for editing.");
        }
    }
}
